package Dao.Process.Imp;

import Dao.Process.StageCheckDao;
import Entity.Process.StageCheck;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StageCheckDaoImp implements StageCheckDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;


    @Override
    public StageCheck getStageCheckBySNOAndPhase(String SNO, String phase, Connection conn) {
        StageCheck stageCheck = null;
        try {
            String sql = "select " +
                    "sno, tno, user_sum, this_sum, opinion, is_pass, state, s_file_name, s_file_url, phase ,result " +
                    "from stage_check " +
                    "where sno = ? and phase = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            ps.setString(2, phase);
            rs = ps.executeQuery();
            if (rs.next()) {
                String sno = rs.getString(1);
                String tno = rs.getString(2);
                Integer userSum = rs.getInt(3);
                Integer thisSum = rs.getInt(4);
                String opinion = rs.getString(5);
                String isPass = rs.getString(6);
                String state = rs.getString(7);
                String sFileName = rs.getString(8);
                String sFileUrl = rs.getString(9);
                String phase1 = rs.getString(10);
                String result = rs.getString(11);
                stageCheck = new StageCheck(sno, tno, userSum, thisSum, opinion, isPass, state, sFileName, sFileUrl, phase1, result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return stageCheck;
    }

    @Override
    public Integer updateCheckBySNOAndState(String SNO, String result, String opinion, String state, Connection conn) {
        System.out.println(SNO);
        System.out.println(state);
        int res = 0;
        try {
            String sql = "update stage_check set state = '审核完成' , opinion = ? , is_pass = ? where sno = ? and phase = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, opinion);
            ps.setString(2, result);
            ps.setString(3, SNO);
            ps.setString(4, state);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer insertState(StageCheck stageCheck, Connection conn) {
        System.out.println(stageCheck);
        int res = 0;
        try {
            String sql = "insert into stage_check(sno, tno, user_sum, this_sum, phase,state,s_file_name , s_file_url )" +
                    " values (?,?,?,?,?,?,?,?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, stageCheck.getSno());
            ps.setString(2, stageCheck.getTno());
            ps.setInt(3, stageCheck.getUserSum());
            ps.setInt(4, stageCheck.getThisSum());
            ps.setString(5, stageCheck.getPhase());
            ps.setString(6, stageCheck.getState());
            ps.setString(7, stageCheck.getSFileName());
            ps.setString(8, stageCheck.getSFileUrl());
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumByTNO(String tno, Integer type, String phase, Connection conn) {
        int res = 0;
        try {
            String sql;
            if (type == 1) {
                sql = "select COUNT(sno) from stage_check where tno = ? and phase = ? ";
            } else {
                sql = "select COUNT(sno) from stage_check where tno = ? and phase = ? and is_pass = '通过' ";
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            ps.setString(2, phase);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumBySNO(String sno, String phase, Connection conn) {
        int res = 0;
        try {
            String sql = "select COUNT(sno) from stage_check where sno = ? and phase = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, sno);
            ps.setString(2, phase);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateReportFile(StageCheck stageCheck, Connection conn) {
        System.out.println(stageCheck);
        int res = 0;
        try {
            String sql = "update stage_check set s_file_name = ?, s_file_url = ? , state = ?, is_pass=?, opinion =? where sno = ? and phase = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, stageCheck.getSFileName());
            ps.setString(2, stageCheck.getSFileUrl());
            ps.setString(3, stageCheck.getState());
            ps.setString(4, stageCheck.getIsPass());
            ps.setString(5, stageCheck.getOpinion());
            ps.setString(6, stageCheck.getSno());
            ps.setString(7, stageCheck.getPhase());
            res = ps.executeUpdate();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }
}
